﻿using System.Collections.Generic;
using PtsNames = Citi.PTSConsolidation.Domain.EntrepotDatabaseNames.BookOfWork;
using System;

namespace Citi.PTSConsolidation.Domain
{
    public partial class Goc
    {
        #region Mappings

        public static readonly Dictionary<string, string> ExcelColumnMappings = new Dictionary<string, string> { 
                { "[GocId]", "GocAttributes" },
                { "[ManagedGeography]", "ManagedGeography" } ,
                { "[RegionName]", "ManagedGeographyDescription" },
                { "[LastUpdate]", "LastUpdate" },
                { "[ReferenceDate]", "ReferenceDate" }
            };

        #endregion

        public static string GenerateDeleteDuplicatedQuery()
        {
            return @"DELETE FROM GOC
                        WHERE Id NOT IN
                        (
	                        SELECT Max(Id) FROM GOC Duplicate
	                        WHERE Duplicate.GocAttributes = GOC.GocAttributes
	                        GROUP BY GOCAttributes
                        )";
        }

        public static string GenerateGocQueryString()
        {
            return string.Format( @"Select [GocId],[ManagedGeography], '{1}' as ReferenceDate, '{2}' as LastUpdate, 
                [RegionName] from [{0}] WHERE [GocId] IS NOT NULL",
                "{0}", DateTime.Today.ToString("yyyy/MM/dd"), DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
        }

        //public static string GenerateGocQueryString(int totalWorksheet)
        //{
        //    var query = "Select [GOC ATTRIBUTE MASTER#GOC ID],[MANAGED GEOGRAPHY], [MAN# GEOGRAPHY DESCRIPTION] from [BR GOC List$] ";

        //    for (int i = 1; i < totalWorksheet; i++)
        //        query += string.Format("Union Select * from [BR GOC List({0})$]  ", i);

        //    return query;
        //}
    }
}
